package com.situ.mvc.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.situ.mvc.dao.IUserDao;
import com.situ.mvc.entity.User;
import com.situ.mvc.util.JDBCUtil;

public class UserDaoImpl implements IUserDao {

	@Override
	public User login(String name, String password) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT id,name,password,email,age FROM user WHERE name=? and password=?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, name);
			preparedStatement.setString(2, password);
			System.out.println(preparedStatement);
			resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				int id = (int) resultSet.getObject("id");
				String userName = resultSet.getString("name");
				String userPassword = resultSet.getString("password");
				String email = resultSet.getString("email");;
				int age = resultSet.getInt("age");;
				User user = new User(userName, userPassword, email, age);
				return user;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, resultSet);
		}
		
		return null;
	
	}

	@Override
	public int findTotalCount() {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		int count = 0;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT count(id) FROM user";
			preparedStatement = connection.prepareStatement(sql);
			System.out.println(preparedStatement);
			resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				count = resultSet.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, resultSet);
		}
		
		return count;
	}

	@Override
	public List<User> pageList(int offset, Integer limit) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		ArrayList<User> list = new ArrayList<>();
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT id,name,password,email,age,joindate FROM user order by id desc limit ?,?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, offset);
			preparedStatement.setInt(2, limit);
			resultSet = preparedStatement.executeQuery();
			System.out.println(preparedStatement);
			while (resultSet.next()) {
				int id = (int) resultSet.getObject("id");
				String name = resultSet.getString("name");
				String password = resultSet.getString("password");
				String email = resultSet.getString("email");
				int age = resultSet.getInt("age");
				//java.sql.Date
				Date joindate = resultSet.getDate("joindate");
				//java.sql.Date是java.util.Date的子类，子类直接可以赋给父类
				//Person p = new Student();学生都是人，但是人不都是学生
				User user = new User(id, name, password, email, age, joindate);
				list.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, resultSet);
		}
		
		return list;
	}

	@Override
	public int deleteById(Integer id) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		int count = 0;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "delete from user where id=?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, id);
			System.out.println(preparedStatement);
			count = preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, null);
		}
		
		return count;
	}

	@Override
	public int add(User user) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		int count = 0;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "INSERT INTO user(name,password,email,age,joindate) VALUES(?,?,?,?,?);";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, user.getName());
			preparedStatement.setString(2, user.getPassword());
			preparedStatement.setString(3, user.getEmail());
			preparedStatement.setInt(4, user.getAge());
			//java里面使用的是java.util.Date，而数据库使用都是java.sql.Date
			//java.sql.Date extends java.util.Date
			//preparedStatement.setDate(5, user.getJoindate());
			//Date date = new Date();
			//将java.util.Date转换成java.sql.Date,因为数据库需要是java.sql.Date
			preparedStatement.setDate(5, 
					new java.sql.Date(user.getJoindate().getTime()));
			System.out.println(preparedStatement);
			count = preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, null);
		}
		
		return count;
	}

	@Override
	public User findById(Integer id) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT id,name,password,email,age FROM user WHERE id=?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, id);
			System.out.println(preparedStatement);
			resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				int userId = (int) resultSet.getObject("id");
				String userName = resultSet.getString("name");
				String userPassword = resultSet.getString("password");
				String email = resultSet.getString("email");;
				int age = resultSet.getInt("age");;
				User user = new User(userId, userName, userPassword, email, age);
				return user;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, resultSet);
		}
		
		return null;
	
	
	}

}
